package com.sigmazero13.bgjeeves.db;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.format.DateFormat;
import android.util.Log;

import com.sigmazero13.bgjeeves.model.GameInfo;
import com.sigmazero13.bgjeeves.model.GameInfoCustom;
import com.sigmazero13.bgjeeves.model.GamePlayInfo;
import com.sigmazero13.bgjeeves.model.GamePlayerInfo;
import com.sigmazero13.bgjeeves.model.SyncInfo;

public class UtilDbHelper {

	public static final int OPERATION_SUCCESS = 0;
	public static final int OPERATION_SUCCESS_WITH_ERRORS = 1;
	public static final int OPERATION_FAIL = 2;
	
	public static final String TAG = "UtilDbHelper";
	
	private Context mContext;
	private SQLiteDatabase mDb;
	
	public UtilDbHelper(Context context) {
		mContext = context;
		GamesDbOpenHelper helper = new GamesDbOpenHelper(mContext);
		mDb = helper.getWritableDatabase();
	}
	
	public Integer getDbVersion() {
		return mDb.getVersion();
	}
	
	/**
	 * Get all the games in the DB
	 * @return A list of all the games
	 */
	public List<GameInfo> selectAllGames() {
		
		List<GameInfo> list = new ArrayList<GameInfo>();
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.GAME_TABLE, 
				DbTable.GAME_TABLE.getCurrentFields(), 
				DbField.GAME_NAME);

		if (cursor.moveToFirst()) {
			do {
				list.add(readGameFromCursor(cursor));
			} while (cursor.moveToNext());
		}
		closeCursor(cursor);
		
		return list;
	}
	
	/**
	 * Select a list of games matching the number of players and game length
	 * @param numPlayers The number of players a game must support
	 * @param maxLength The maximum length the game must support; Only games
	 * which can be played in time less than this will be returned
	 * @return A list of matching games
	 */
	public List<GameInfo> selectMatchingGames(Integer numPlayers, Integer maxLength) {
		List<GameInfo> matchingGames = new ArrayList<GameInfo>();
		for (GameInfo game : selectAllGames()) {
			if (numPlayers >= game.getMinPlayers() &&
					numPlayers <= game.getMaxPlayers() &&
					maxLength >= game.getBggLength()) {
				matchingGames.add(game);
			}
		}
		
		return matchingGames;
	}
	
	/**
	 * Get the game information for a single game
	 * @param gameId The ID of the game to be selected
	 * @return An object containing the game information
	 */
	public GameInfo selectSingleGame(Integer gameId) {
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.GAME_TABLE, 
				DbTable.GAME_TABLE.getCurrentFields(), 
				DbField.GAME_ID, gameId.toString(), DbField.GAME_NAME);
		
		GameInfo game = new GameInfo();
		if (cursor.moveToFirst()) {
			game = readGameFromCursor(cursor);
		}
		closeCursor(cursor);
		
		return game;
	}
	
	/**
	 * Record the customized information for a game
	 * @param custom The customized information to record
	 */
	public void recordCustomInfo(GameInfoCustom custom) {
		if (custom == null || custom.getParentGame() == null) return;
		if (custom.isEmpty()) {
			deleteCustomInfoForGame(custom.getParentGame().getGameId());
			return;
		}
		
		String gameId = custom.getParentGame().getGameId().toString();		
		ContentValues cv = new ContentValues();
		cv.put(DbField.CUSTOM_INFO_GAME_ID.getName(), custom.getParentGame().getGameId());
		cv.put(DbField.CUSTOM_INFO_MINPLAYERS.getName(), custom.getMinPlayers());
		cv.put(DbField.CUSTOM_INFO_MAXPLAYERS.getName(), custom.getMaxPlayers());
		
		DbQueryRunner.updateOrInsertRow(mDb, DbTable.CUSTOM_INFO_TABLE, cv, DbField.CUSTOM_INFO_GAME_ID, gameId);
	}
	
	/**
	 * Delete the customized information for a game
	 * @param gameId The id of the game to remove the custom info from
	 */
	public void deleteCustomInfoForGame(Integer gameId) {
		DbQueryRunner.deleteRow(mDb, DbTable.CUSTOM_INFO_TABLE, DbField.CUSTOM_INFO_GAME_ID, gameId.toString());
	}
	
	/**
	 * Get the customized information for a game
	 * @param game The game to get the custom info for
	 * @return The customized info for the game
	 */
	public GameInfoCustom getCustomInfoForGame(GameInfo game) {
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.CUSTOM_INFO_TABLE,
				new DbField[] {DbField.CUSTOM_INFO_MINPLAYERS, DbField.CUSTOM_INFO_MAXPLAYERS},
				DbField.CUSTOM_INFO_GAME_ID, game.getGameId().toString(), null);
		GameInfoCustom custom = null;
		if (cursor.moveToFirst()) {
			custom = new GameInfoCustom(game);
			Integer min = getCursorInt(cursor, DbField.CUSTOM_INFO_MINPLAYERS);
			Integer max = getCursorInt(cursor, DbField.CUSTOM_INFO_MAXPLAYERS);
			if (min > 0)
				custom.setMinPlayers(min);
			if (max > 0) 
				custom.setMaxPlayers(max);
		} 
		closeCursor(cursor);
		
		return custom;
	}
	
	/**
	 * Get the number of games listed in your collection.
	 * @return The number of games in the collection
	 */
	public Integer getGameCollectionSize() {
		Cursor cursor = DbQueryRunner.runCount(mDb, DbTable.GAME_TABLE, null, null);
		Integer size = 0;
		if (cursor.moveToFirst()) {
			size = cursor.getInt(0);
		}
		closeCursor(cursor);
		return size;
	}
	
	/**
	 * Sychronize a list of games with those found in the DB, recording the amount
	 * of new games added/deleted from the DB.
	 * @param games
	 * @return
	 */
	public SyncInfo syncListWithDb(List<GameInfo> games) {
		SyncInfo sync = new SyncInfo();
		mDb.beginTransaction();
		try {
			List<Integer> gameIds = new ArrayList<Integer>();
			for (GameInfo game : games) {
				gameIds.add(game.getGameId());
				if (insertGame(game) != -1) {
					sync.incrementAdd();
				} else if (modifyGame(game)) {
					sync.incrementModified();
				}
			}
			deleteAllGamesExcept(gameIds, sync);
			mDb.setTransactionSuccessful();
		} finally {
			mDb.endTransaction();
		}
		return sync;
	}
	
	/** 
	 * Record a play of a game
	 * @param play The game play information to record
	 * @param playId If 0, creates a new play; otherwise, updates an existing play
	 * @return OPERATION_FAIL, OPERATION_SUCCESS, or OPERATION_SUCCESS_WITH_ERRORS, depending on what happens.
	 */
	public int recordPlay(GamePlayInfo play, long playId) {
		mDb.beginTransaction();
		ContentValues cv = new ContentValues();
		cv.put(DbField.PLAY_GAME_ID.getName(), play.getGame().getGameId());
		String gameDate = "";
		if (play.getGameDate() != null)	
			gameDate = DateFormat.getDateFormat(mContext).format(play.getGameDate());
		cv.put(DbField.PLAY_DATE.getName(), gameDate);
		cv.put(DbField.PLAY_LOCATION.getName(), play.getLocation());
		cv.put(DbField.PLAY_SCENARIO.getName(), play.getScenario());
		cv.put(DbField.PLAY_LENGTH.getName(), play.getGameLength());
		cv.put(DbField.PLAY_COMMENTS.getName(), play.getComments());
		
		try {
			long newId = DbQueryRunner.updateOrInsertRow(mDb, DbTable.PLAY_TABLE, cv, DbField.PLAY_ID, String.valueOf(playId));
			DbQueryRunner.deleteRow(mDb, DbTable.PLAYER_TABLE, DbField.PLAYER_PLAY_ID, String.valueOf(playId));
			
			if (newId > 0) {
				playId = newId;
				play.setPlayId(newId);
			}
		} catch (Throwable t) {
			Log.w(TAG, "Error recording play to database", t);
			mDb.endTransaction();
			return OPERATION_FAIL;
		}

		int returnCode = OPERATION_SUCCESS;
		for (GamePlayerInfo player : play.getPlayers()) {
			int newCode = recordPlayerForPlay(player, playId, play.getGame().getGameId());
			if (returnCode == OPERATION_SUCCESS && newCode != OPERATION_SUCCESS) {
				returnCode = OPERATION_SUCCESS_WITH_ERRORS;
			}
		}
		mDb.setTransactionSuccessful();
		mDb.endTransaction();
		return returnCode;
	}
	
	
	private int recordPlayerForPlay(GamePlayerInfo player, long playId, int gameId) {
		int returnCode = OPERATION_SUCCESS;
		
		ContentValues cv = new ContentValues();
		cv.put(DbField.PLAYER_PLAY_ID.getName(), playId);
		cv.put(DbField.PLAYER_NAME.getName(), player.getName());
		cv.put(DbField.PLAYER_USERNAME.getName(), player.getUsername());
		cv.put(DbField.PLAYER_STARTINGPOS.getName(), player.getStartingPos());
		cv.put(DbField.PLAYER_SCORE.getName(), player.getScore());
		cv.put(DbField.PLAYER_WINNER.getName(), player.getWinner()?1:0);
		try {
			long playerId = mDb.insertWithOnConflict(DbTable.PLAYER_TABLE.getName(), null, cv, SQLiteDatabase.CONFLICT_ABORT);
			
			Log.i(TAG, "Found playerId " + playerId + ", adding teams...");
			
			for (String team : player.getTeams()) {
				if (team == null) continue;
				team = team.trim();
				if (team.equals("")) continue;
				ContentValues tcv = new ContentValues();
				tcv.put(DbField.TEAM_GAME_ID.getName(), gameId);
				tcv.put(DbField.TEAM_NAME.getName(), team);
				long teamId = DbQueryRunner.getIdForRow(mDb, DbTable.TEAM_TABLE, tcv, new DbField[] {DbField.TEAM_GAME_ID, DbField.TEAM_NAME});
				
				ContentValues pcv = new ContentValues();
				pcv.put(DbField.TEAM_PLAYER_PLAYER_ID.getName(), playerId);
				pcv.put(DbField.TEAM_PLAYER_TEAM_ID.getName(), teamId);
				
				mDb.insert(DbTable.TEAM_PLAYER_TABLE.getName(), null, pcv);
			}
		} catch (Throwable t) {
			Log.w(TAG, "Error recording player info for play!", t);
			returnCode = OPERATION_SUCCESS_WITH_ERRORS;
		}
	
		
		return returnCode;
	}
	
	/**
	 * Get the number of plays recorded for a specific game
	 * @param gameId The id of the game to check
	 * @return The number of plays recorded for the game
	 */
	public Integer getNumPlaysForGame(Long gameId) {
		Cursor cursor = DbQueryRunner.runCount(mDb, DbTable.PLAY_TABLE, DbField.PLAY_GAME_ID, gameId.toString());
		Integer numPlays = 0;
		if (cursor.moveToFirst()) {
			numPlays = cursor.getInt(0);
		}
		closeCursor(cursor);
		
		return numPlays;
	}
	
	/**
	 * Get a single record of game play information
	 * @param playId The id of the game play record
	 * @return The game play information of the specified record
	 */
	public GamePlayInfo selectSinglePlay(Long playId) {
		GamePlayInfo play = null;
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.PLAY_TABLE, 
				DbTable.PLAY_TABLE.getCurrentFields(), 
				DbField.PLAY_ID, playId.toString(), DbField.PLAY_ID);
		
		if (cursor.moveToFirst()) {
			play = getPlayInfoHelper(cursor);
			play.setGame(selectSingleGame(getCursorInt(cursor, DbField.PLAY_GAME_ID)));
		}
		closeCursor(cursor);
		
		play.getPlayers().addAll(getPlayersForPlay(playId));
		
		return play;
	}
	
	/**
	 * Get all plays for all games
	 * @param getPlayers If true, will retrieve the player information in the record; otherwise 
	 * only the overall play info will be retrieved.
	 * @return A list of game play records
	 */
	public List<GamePlayInfo> getAllPlays(boolean getPlayers) {
		List<GamePlayInfo> plays = new ArrayList<GamePlayInfo>();
		Map<Integer, GameInfo> games = new HashMap<Integer, GameInfo>();
		
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.PLAY_TABLE, 
				DbTable.PLAY_TABLE.getCurrentFields(), 
				DbField.PLAY_ID);
		
		if (cursor.moveToFirst()) {
			do {
				GamePlayInfo play = new GamePlayInfo();
				play = getPlayInfoHelper(cursor);
				Integer gameId = getCursorInt(cursor, DbField.PLAY_GAME_ID);
				GameInfo game;
				if (games.containsKey(gameId)) {
					game = games.get(gameId);
				} else {
					game = selectSingleGame(gameId);
					games.put(gameId, game);
				}
				
				play.setGame(game);
				
				plays.add(play);
			} while (cursor.moveToNext());
		}
		closeCursor(cursor);
		
		for (GamePlayInfo play : plays) {
			if (getPlayers) {
				play.getPlayers().addAll(getPlayersForPlay(play.getPlayId()));
			} else {
				play.setNumPlayers(getNumPlayersForPlay(play.getPlayId()));
			}
		}
		
		Collections.sort(plays);
		Collections.reverse(plays);
		
		return plays;
	}
	
	/**
	 * Get all plays for a specific game without player information
	 * @param gameId The id of the game to retrieve play information for
	 * @return A list of play records for the specified game
	 */
	public List<GamePlayInfo> getPlaysForGame(Integer gameId) {
		return getPlaysForGame(gameId, false);
	}
	
	/**
	 * Gets all plays for a specific game
	 * @param gameId The id of the game to retrieve play information for
	 * @param getPlayers If true, will retrieve the player information for each record; otherwise, only
	 * the overall play info for each record will be retrieved.
	 * @return A list of play records for the specified game
	 */
	public List<GamePlayInfo> getPlaysForGame(Integer gameId, boolean getPlayers) {
		List<GamePlayInfo> plays = new ArrayList<GamePlayInfo>();
		
		if (gameId == 0) return getAllPlays(getPlayers);
		
		GameInfo game = selectSingleGame(gameId);
		
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.PLAY_TABLE, 
				DbTable.PLAY_TABLE.getCurrentFields(), 
				DbField.PLAY_GAME_ID, gameId.toString(), DbField.PLAY_ID);
		
				
		if (cursor.moveToFirst()) {
			do {
				GamePlayInfo play = new GamePlayInfo();
				play = getPlayInfoHelper(cursor);
				play.setGame(game);
				
				plays.add(play);
			} while (cursor.moveToNext());
		}
		closeCursor(cursor);
		
		for (GamePlayInfo play : plays) {
			if (getPlayers) {
				play.getPlayers().addAll(getPlayersForPlay(play.getPlayId()));
			} else {
				play.setNumPlayers(getNumPlayersForPlay(play.getPlayId()));
			}
		}
		
		Collections.sort(plays);
		Collections.reverse(plays);
		
		return plays;
	}
		
	/**
	 * Gets the player information for a game play record
	 * @param playId The id of the game play record
	 * @return A list of player information for the specified play record
	 */
	public List<GamePlayerInfo> getPlayersForPlay(Long playId) {
		List<GamePlayerInfo> players = new ArrayList<GamePlayerInfo>();
		Map<Integer, GamePlayerInfo> playerMap = new HashMap<Integer, GamePlayerInfo>();
		if (playId != null) {
			Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.PLAYERS_WITH_TEAMS_VIEW,
				DbTable.PLAYERS_WITH_TEAMS_VIEW.getCurrentFields(),
				DbField.PLAYER_PLAY_ID, playId.toString(), DbField.PLAYER_STARTINGPOS);
			
			if (cursor.moveToFirst()) {
				do {
					Integer playerId = getCursorInt(cursor, DbField.PLAYER_ID);
					GamePlayerInfo player;
					if (!playerMap.containsKey(playerId)) {
						player = new GamePlayerInfo();
						
						player.setName(getCursorString(cursor, DbField.PLAYER_NAME));
						player.setUsername(getCursorString(cursor, DbField.PLAYER_USERNAME));
						player.setStartingPos(getCursorInt(cursor, DbField.PLAYER_STARTINGPOS));
						player.setScore(getCursorInt(cursor, DbField.PLAYER_SCORE));
						player.setWinner(getCursorInt(cursor, DbField.PLAYER_WINNER)==1);
						
						players.add(player);
						playerMap.put(playerId, player);
					} else {
						player = playerMap.get(playerId);
					}
					String newTeam = getCursorString(cursor, DbField.TEAM_NAME);
					if (newTeam != null && !newTeam.equals("")) {
						player.addTeam(newTeam);
					}
				} while (cursor.moveToNext());
			}
			
			closeCursor(cursor);
		}
		return players;
	}
	
	/**
	 * Gets the number of players recorded for a specific play record
	 * @param playId The id of the play record to get player count for
	 * @return The number of players recorded for the play
	 */
	public Integer getNumPlayersForPlay(Long playId) {
		Cursor cursor = DbQueryRunner.runCount(mDb, DbTable.PLAYER_TABLE, DbField.PLAYER_PLAY_ID, playId.toString());
		
		Integer numPlayers = 0;
		if (cursor.moveToFirst()) {
			numPlayers = cursor.getInt(0);
		}
		closeCursor(cursor);
		
		return numPlayers;
	}
	
	/**
	 * Get a list of all player names recorded thus far
	 * @return A list of all players found in the database
	 */
	public List<String> getPlayerNames() {
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.PLAYER_TABLE, 
				new DbField[]{DbField.PLAYER_NAME}, 
				DbField.PLAYER_NAME);
		
		List<String> players = buildListFromCursor(cursor);
		closeCursor(cursor);
		
		return players;
	}
	
	/**
	 * Renames a player
	 * @param oldName The name of the player to rename
	 * @param newName The new name of the player
	 * @return true if any replacements were made, false if not
	 */
	public boolean renamePlayer(String oldName, String newName) {
		ContentValues cv = new ContentValues();
		cv.put(DbField.PLAYER_NAME.getName(), newName);
		
		String whereString = DbField.PLAYER_NAME.getName()+"=?";
		
		String[] whereParams = { oldName };
		
		int numMod = mDb.update(DbTable.PLAYER_TABLE.getName(), cv, whereString, whereParams);
		
		return numMod > 0;
	}
	
	
	/**
	 * Get the team ID for a particular team matching a game
	 * @param teamName The name of the team to find
	 * @param gameId The game ID to match the team for
	 * @return
	 */
	public Integer getTeamIdForGame(String teamName, Integer gameId) {
		Integer teamId = 0;
		
		Cursor c = DbQueryRunner.runQuery(mDb, DbTable.TEAM_TABLE, 
			new DbField[] {DbField.TEAM_ID}, 
			new DbField[] {DbField.TEAM_NAME, DbField.TEAM_GAME_ID}, 
			new String[] {teamName, gameId.toString()}, null);
		if (c.moveToFirst()) {
			// Find the team ID
			teamId = getCursorInt(c, DbField.TEAM_ID);
			closeCursor(c);
		}
		
		return teamId;
	}
	
	
	/**
	 * Rename a team for a specific game; Teams for other games will not be affected, even if they
	 * have the same team name
	 * @param oldTeam The name of the team to change
	 * @param newTeam The new name of the team
	 * @param gameId The ID of the game to change the team for 
	 * @return True if at least one change was made, false otherwise
	 */
	public boolean renameTeamForGame(String oldTeam, String newTeam, Integer gameId) {
		Integer rowsChanged = 0;
		
		Integer newTeamId = getTeamIdForGame(newTeam, gameId);
		if (newTeamId > 0) {
			Integer oldTeamId = getTeamIdForGame(oldTeam, gameId);
						
			// change all the team-player connections from old team to new team
			ContentValues cv = new ContentValues();
			cv.put(DbField.TEAM_PLAYER_TEAM_ID.getName(), newTeamId);
			DbQueryRunner.changeTableValue(mDb, DbTable.TEAM_PLAYER_TABLE, cv, 
					DbField.TEAM_PLAYER_TEAM_ID, oldTeamId.toString());
			
			rowsChanged = DbQueryRunner.countChanges(mDb);
			
			// delete the old team
			DbQueryRunner.deleteRow(mDb, DbTable.TEAM_TABLE, DbField.TEAM_NAME, oldTeam);
		}
		else {
			ContentValues cv = new ContentValues();
			cv.put(DbField.TEAM_NAME.getName(), newTeam);
			DbQueryRunner.changeTableValue(mDb, DbTable.TEAM_TABLE, cv, DbField.TEAM_NAME, oldTeam);
			
			rowsChanged = DbQueryRunner.countChanges(mDb);
		}
		
		return rowsChanged > 0;
	}
	
	
	public boolean addTeamForGame(String team, Integer gameId) {
		ContentValues cv = new ContentValues();
		cv.put(DbField.TEAM_GAME_ID.getName(), gameId);
		cv.put(DbField.TEAM_NAME.getName(), team);
		
		long newId = DbQueryRunner.getIdForRow(mDb, DbTable.TEAM_TABLE, cv, 
				new DbField[] {DbField.TEAM_GAME_ID, DbField.TEAM_NAME});
		
		return newId != 0;
	}
	
	
	public void deleteTeamForGame(String team, Integer gameId) {
		DbQueryRunner.deleteRow(mDb, DbTable.TEAM_TABLE,
				new DbField[] {DbField.TEAM_NAME, DbField.TEAM_GAME_ID},
				new String[] {team, gameId.toString()});
	}
	
	
	/**
	 * Get a list of all player usernames recorded thus far
	 * @return A list of all usernames found in the database
	 */
	public List<String> getPlayerUsernames() {
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.PLAYER_TABLE, 
				new DbField[]{DbField.PLAYER_USERNAME}, 
				DbField.PLAYER_USERNAME);
		
		List<String> usernames = buildListFromCursor(cursor);
		closeCursor(cursor);
		
		return usernames;
	}
	
	/**
	 * Get a list of all locations recorded thus far
	 * @return A list of all locations found in the database
	 */
	public List<String> getLocations() {
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.PLAY_TABLE, 
				new DbField[]{DbField.PLAY_LOCATION}, 
				DbField.PLAY_LOCATION);
		
		List<String> locations = buildListFromCursor(cursor);
		closeCursor(cursor);
		
		return locations;
	}
	
	/**
	 * Get a list of all teams recorded for a specific game
	 * @param gameId The id of the game to retrieve the team list for
	 * @return A list of teams for the specified game
	 */
	public List<String> getTeamsForGame(Integer gameId) {
		Cursor cursor = DbQueryRunner.runQuery(mDb, DbTable.TEAM_TABLE, new DbField[] {DbField.TEAM_NAME}, 
			DbField.TEAM_GAME_ID, gameId.toString(), DbField.TEAM_NAME);
				
		List<String> teams = buildListFromCursor(cursor);
		closeCursor(cursor);
		
		return teams;
	}
	
	/**
	 * Get a list of all scenarios recorded for a specific game
	 * @param gameId The id of the game to retrieve the scenario list for 
	 * @return A list of scenarios for the specified game
	 */
	public List<String> getScenariosForGame(Integer gameId) {
		Cursor cursor = DbQueryRunner.runQuery(mDb, 
				DbTable.SCENARIOS_VIEW, 
				new DbField[] {DbField.PLAY_SCENARIO},
				DbField.PLAY_GAME_ID, 
				gameId.toString(), 
				DbField.PLAY_SCENARIO);
				
		List<String> scenarios = buildListFromCursor(cursor);
		closeCursor(cursor);
		
		return scenarios;
	}
	
	
	
	
	/**
	 * Insert a game into the DB, if it's not already there
	 * @param game The game to insert
	 * @return The row ID of the game inserted, or SQLITE_CONSTRAINT if it was already there 
	 */
	private long insertGame(GameInfo game) {
		ContentValues cv = new ContentValues();
		cv.put(DbField.GAME_ID.getName(), game.getGameId());
		cv.put(DbField.GAME_NAME.getName(), game.getName());
		cv.put(DbField.GAME_MINPLAYERS.getName(), game.getMinPlayers());
		cv.put(DbField.GAME_MAXPLAYERS.getName(), game.getMaxPlayers());
		cv.put(DbField.GAME_BGGLENGTH.getName(), game.getBggLength());
		cv.put(DbField.GAME_BGGRATING.getName(), game.getBggRating());
		cv.put(DbField.GAME_MANUAL.getName(), game.isManualEntry()?1:0);
		
		try {
			long newid = mDb.insertWithOnConflict(DbTable.GAME_TABLE.getName(), null, cv, SQLiteDatabase.CONFLICT_ABORT);
			return newid;
		} catch (Throwable t) {
			return -1;
		}
	}
	
	/**
	 * Modify a game in the DB, updating stats as necessary
	 * @param game The game to modify values to.
	 * @return True if the game exists and was modified, false otherwise
	 */
	private boolean modifyGame(GameInfo game) {
		
		ContentValues cv = new ContentValues();
		cv.put(DbField.GAME_MINPLAYERS.getName(), game.getMinPlayers());
		cv.put(DbField.GAME_MAXPLAYERS.getName(), game.getMaxPlayers());
		cv.put(DbField.GAME_BGGLENGTH.getName(), game.getBggLength());
		cv.put(DbField.GAME_BGGRATING.getName(), game.getBggRating());
		
		String whereString = DbField.GAME_ID.getName()+"=? AND (" +
							DbField.GAME_MINPLAYERS.getName()+"<>? OR " +
							DbField.GAME_MAXPLAYERS.getName()+"<>? OR " +
							DbField.GAME_BGGLENGTH.getName()+"<>? OR " +
							DbField.GAME_BGGRATING.getName()+"<>?)";
		
		String[] whereParams = { game.getGameId().toString(),
							game.getMinPlayers().toString(),
							game.getMaxPlayers().toString(),
							game.getBggLength().toString(),
							game.getBggRating().toString()
		};
		
		int numMod = mDb.update(DbTable.GAME_TABLE.getName(), cv, whereString, whereParams);
		
		return numMod > 0;
	}
	
	/**
	 * Delete a single game from the DB
	 * @param game The game to delete
	 */
	private void deleteGame(Integer gameId) {
		DbQueryRunner.deleteRow(mDb, DbTable.GAME_TABLE, DbField.GAME_ID, gameId.toString());
		DbQueryRunner.deleteRow(mDb, DbTable.CUSTOM_INFO_TABLE, DbField.CUSTOM_INFO_GAME_ID, gameId.toString());
	}
		
	/**
	 * Remove all games from the collection except those in the list provided.
	 * @param gameIds A list of gameIds to keep in the database
	 * @param sync An object to record the number of games deleted
	 */
	private void deleteAllGamesExcept(List<Integer> gameIds, SyncInfo sync) {
		for (GameInfo game : selectAllGames()) {
			Integer gameId = game.getGameId();
			if (!gameIds.contains(gameId)) {
				deleteGame(gameId);
				sync.incrementDeleted();
			}
		}
	}
	
	/**
	 * Reads a game from a cursor object
	 * @param cursor The cursor object (IE row of the database)
	 * @return The game information contained in that row
	 */
	private GameInfo readGameFromCursor(Cursor cursor) {
		GameInfo game = new GameInfo();	
		game.setGameId(getCursorInt(cursor, DbField.GAME_ID));
		game.setName(getCursorString(cursor, DbField.GAME_NAME));
		game.setMinPlayers(getCursorInt(cursor, DbField.GAME_MINPLAYERS));
		game.setMaxPlayers(getCursorInt(cursor, DbField.GAME_MAXPLAYERS));
		game.setBggLength(getCursorInt(cursor, DbField.GAME_BGGLENGTH));
		game.setBggRating(getCursorDouble(cursor, DbField.GAME_BGGRATING));
		game.setManualEntry(getCursorInt(cursor, DbField.GAME_MANUAL)==1);
		
		game.setCustomInfo(getCustomInfoForGame(game));
		
		return game;
	}
	
	private GamePlayInfo getPlayInfoHelper(Cursor cursor) {
		GamePlayInfo play = new GamePlayInfo();
		play.setPlayId((cursor.getLong(0)));
		try {
			Date d = DateFormat.getDateFormat(mContext).parse(getCursorString(cursor, DbField.PLAY_DATE));
			play.setGameDate(d);
		} catch (ParseException pe) {
			//NOTHING
		}
		play.setLocation(getCursorString(cursor, DbField.PLAY_LOCATION));
		play.setScenario(getCursorString(cursor, DbField.PLAY_SCENARIO));
		play.setGameLength(getCursorInt(cursor, DbField.PLAY_LENGTH));
		play.setComments(getCursorString(cursor, DbField.PLAY_COMMENTS));
		
		return play;
	}	
	
	private List<String> buildListFromCursor(Cursor c) {
		List<String> list = new ArrayList<String>();
		if (c.moveToFirst()) {
			do {
				String item = c.getString(0);
				if (item != null)
					list.add(item);
			} while (c.moveToNext());
		}
		return list;
	}
	
	private String getCursorString(Cursor c, DbField field) {
		return c.getString(c.getColumnIndex(field.getName()));
	}
	
	private Integer getCursorInt(Cursor c, DbField field) {
		return c.getInt(c.getColumnIndex(field.getName()));
	}
	
	private Double getCursorDouble(Cursor c, DbField field) {
		return c.getDouble(c.getColumnIndex(field.getName()));
	}
	
	private void closeCursor(Cursor cursor) {
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
	}
	
}
